package com.javaweb.dao.impl;

import com.javaweb.common.StringUtils;
import com.javaweb.dao.RoleDao;
import com.javaweb.model.Permission;
import com.javaweb.model.Role;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/9/24 22:44
 */

/**
 * todo:完成如下所有方法
 */
@Repository
@Slf4j
public class RoleDaoImpl implements RoleDao {

    @Autowired
    private Connection conn ;

    @Override
    public Integer insert(Role role) {
        //插入role表
        int res = 0;
        String sql = "INSERT INTO tb_role (name) VALUES"+
                "(?)" ;
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, role.getName()) ;
            res = ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert user error: " + e.getMessage()) ;
        }
        return res ;
    }

    @Override
    public Integer insertRolePerm(Role role, Permission perm) {
        int res = 0;
        String sql = "INSERT INTO tb_role_per (role_id,per_id) value (?,?)";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1, role.getRoleId());
            ps.setInt(2, perm.getPerId());
            res = ps.executeUpdate() ;
        } catch (SQLException e) {
            log.error("execute insert user error: " + e.getMessage()) ;
        }
        return res;
    }

    @Override
    public List<Role> selectRolesByUserName(String username) {

        List<Role> roles = new ArrayList<>();
        String sql = "SELECT t1.role_id,t1.`name`\n" +
                "from tb_role t1\n" +
                "left join tb_user_role t2 on t1.role_id = t2.role_id\n" +
                "left join tb_user t3 on t2.user_id = t3.user_id\n" +
                "where t3.`name` = ?";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setString(1, username);
            ResultSet res = ps.executeQuery() ;
            while (res.next()){
                Role role = new Role();
                role.setRoleId(res.getInt(1));
                role.setName(res.getString(2));
                roles.add(role);
            }
        } catch (SQLException e) {
            log.error("execute insert user error: " + e.getMessage()) ;
        }
        return roles;
    }

    @Override
    public List<Role> selectRolesByUserId(int userId) {
        List<Role> roles = new ArrayList<>();
        String sql = "SELECT t1.role_id,t1.`name`\n" +
                "from tb_role t1\n" +
                "left join tb_user_role t2 on t1.role_id = t2.role_id\n" +
                "left join tb_user t3 on t2.user_id = t3.user_id\n" +
                "where t2s.`user_id` = ?";
        try (
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ) {
            ps.setInt(1, userId);
            ResultSet res = ps.executeQuery() ;
            while (res.next()){
                Role role = new Role();
                role.setRoleId(res.getInt(1));
                role.setName(res.getString(2));
                roles.add(role);
            }
        } catch (SQLException e) {
            log.error("execute insert user error: " + e.getMessage()) ;
        }
        return roles;
    }

    @Override
    public void deleteByRoleId(int roleId) {

        String sql = "DELETE FROM tb_role WHERE role_id = ?";
        try(
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ){
            ps.setInt(1,roleId);
            ps.executeUpdate();
        }catch (SQLException e){
            log.error("execute insert user error: " + e.getMessage()) ;
        }

    }

    @Override
    public void deleteUserRole(int userId, int roleId) {

        String sql = "DELETE FROM tb_user_role WHERE role_id = ? and user_id = ?";
        try(
                PreparedStatement ps = this.conn.prepareStatement(sql)
        ){
            ps.setInt(1,roleId);
            ps.setInt(2,userId);
            ps.executeUpdate();
        }catch (SQLException e){
            log.error("execute insert user error: " + e.getMessage()) ;
        }

    }
}
